package com.penglecode.datafetch.taobao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.support.JdbcUtils;

public class FetchedDataDatabaseWriter {

	private static final Log logger = LogFactory.getLog(FetchedDataDatabaseWriter.class);
	
	private String user = "apps";
	
	private String password = "apps";
	
	private String url = "jdbc:mysql://127.0.0.1:3306/dev";
	
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, password);
	}
	
	public void batchInsertFetchedData(List<ProductInfo> productInfoList){
		if(productInfoList == null || productInfoList.isEmpty()){
			return;
		}
		String sql = "insert into tb_list_product(product_id, product_name, product_detail_page_url, product_list_picture_url, price, shipping_fee, dealing_count, comment_count, store_name, store_short_address, create_time)"
				  + " values(?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);
			for(int i = 0; i < productInfoList.size(); i++){
				pstmt.setString(1, productInfoList.get(i).getProductId());
				pstmt.setString(2, productInfoList.get(i).getProductName());
				pstmt.setString(3, productInfoList.get(i).getProductDetailPageUrl());
				pstmt.setString(4, productInfoList.get(i).getProductListPictureUrl());
				pstmt.setDouble(5, productInfoList.get(i).getPrice());
				pstmt.setDouble(6, productInfoList.get(i).getShippingFee());
				pstmt.setInt(7, productInfoList.get(i).getDealingCount());
				pstmt.setInt(8, productInfoList.get(i).getCommentCount());
				pstmt.setString(9, productInfoList.get(i).getStoreName());
				pstmt.setString(10, productInfoList.get(i).getStoreShortAddress());
				pstmt.setString(11, DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss"));
				pstmt.addBatch();
				if((i + 1) % 2000 == 0){
					pstmt.executeBatch();
				}
			}
			pstmt.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.closeStatement(pstmt);
			JdbcUtils.closeConnection(conn);
		}
		
	}
	
	public static void main(String[] args) {
		FetchedDataDatabaseWriter databaseWriter = new FetchedDataDatabaseWriter();
		DataFetcher<List<ProductInfo>> fetcher = null;
		String[] keywords = new String[]{"超极本 超薄", "galaxy", "iphone"};
		for(String keyword : keywords){
			for(int i = 0; i < 100; i++){
				int currentPageIndex = i;
				int pageSize = 40;
				String searchPageUrl = "http://s.taobao.com/search?q=" + keyword + "&commend=all&ssid=s5-e&search_type=item&sourceId=tb.index&sort=credit-desc&spm=1.1000386.5803581.d4908513&s=" + (currentPageIndex * pageSize);
				fetcher = new TaoBaoListingProductDataFetcher(searchPageUrl);
				System.out.println(searchPageUrl);
				try {
					List<ProductInfo> productInfoList = fetcher.doFetching();
					System.out.println(">>> keyword = " + keyword + ", page = " + currentPageIndex +", size = " + (productInfoList == null ? 0 : productInfoList.size()));
					databaseWriter.batchInsertFetchedData(productInfoList);
				} catch (Exception e) {
					logger.error(e, e);
				}
			}
		}
	}
	
}
